-- STANDARD SCRIPT
-- OUTPUT DATA FOR RECOn PROCESS 
--
--


------------------------------------------------
-- FUNCTION TO INSERT NEW ROWS INTO ACCOUNT TABLE


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION insurance2_output_recon_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;

BEGIN


SELECT INTO v_batch_run_date,v_cust_id our_run_date,cust_id from batch_insurance2_t limit 1;


-- STEP 1 - SCRATCH insurance2_output_recon_t
--

TRUNCATE insurance2_output_recon_t;


-- STEP 2 - INSERT INTO insurance2_output_recon_t
--

RAISE NOTICE 'START INSERT INTO insurance2_output_recon_t TABLE';

INSERT INTO insurance2_output_recon_t
SELECT
  'M' as data_flag,
  NULL as seqnum ,
  b.recon_ind   ,
  b.recon_exclude,
  b.recon_full_scenario,
  b.customer_type ,
  b.processing_type ,
  NULL as emdeon_ind ,
  a.cust_id ,
  a.hid     ,
  a.acctnum ,
  a.lname   ,
  a.client_field1,
  a.client_field2,
  a.client_field3,
  a.client_date1 ,
  a.pat_proc_type,
  a.pat_type     ,
  a.our_run_date ,
  a.file_date    ,
  a.our_run_date ,
  a.service_date_begin ,
  a.service_date_end   ,
  a.charges            ,
  a.balance            ,
  a.insurance_desc     ,
  a.carrier_code       ,
  a.plan_type          ,
  NULL as date_billed ,
  NULL as date_rejected,
  NULL as prior_ins_date,
  NULL as paid_date     ,
  NULL as amount_paid   ,
  NULL as note          ,
  NULL as medlytix_note ,
  NULL as validate_status ,
  NULL as validated_date,
  NULL as multiple_row  ,
  NULL as warning_doublepay,
  NULL as user_id       ,
  NULL as recon_sent_date,
  a.medlytix_ins_type

FROM

  (SELECT * FROM insurance2_results_t WHERE cust_id = v_cust_id AND our_run_date = v_batch_run_date AND active_flag = 1 AND suppress_flag = 0) as a

   JOIN

  (
    SELECT distinct on (cust_id,last_run_date) cust_id,last_run_date,recon_ind,recon_exclude,recon_full_scenario,customer_type,processing_type 
    FROM batch_status_insurance1_t
  ) as b 

   ON

  (a.cust_id=b.cust_id) AND (a.our_run_date=b.last_run_date)
;


GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'DONE INSERT INTO insurance2_output_recon_t TABLE : %', v_status;


  return v_status;
END;
$$ LANGUAGE plpgsql;
